﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;using System.Configuration;
using System.Windows.Forms;

namespace ChairtyBank.DAL
{
    class InstalmentEntity
    {
        public Int32 Id { get; set; }
        public DateTime DueDate { get; set; }
        public Int32 Amount { get; set; }
        public Int32 Penalty { get; set; }
        public Int32 Total { get; set; }
        public DateTime DateOfPay { get; set; }
        public String Notes { get; set; }     
        public Int32 Loan_ID { get; set; }
        public Int32 Payed_Amount { get; set; }
        public bool payed { get; set; }
        public Int32 Rest { get; set; }
        public Int32 userId { get; set; }


    }

    /*
         Class Client which contains all methods related to Customer table
     */

    class Instalment
    {
        //Set absolute DB path rather than relative to solve openFileDialog reset path problem
        private static string conString = ConfigurationManager.ConnectionStrings["ChairtyBank"].ToString();
        private static string cmdText = "";

   
        public static List<InstalmentEntity> GetInstalment(int Loan_ID)
        {
            List<InstalmentEntity>  IList = new List<InstalmentEntity>();

            cmdText = "select * from Pay where Loan_ID=@Loan_ID Order By Instalment_ID ASC";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Loan_ID", Loan_ID);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while(reader.Read())
                {

                    IList.Add(fillEntityFromReader(reader));
                }
          //      MessageBox.Show("Get"+IList.Count+ "Records");
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if(con.State == ConnectionState.Open)
                    con.Close();
            }

            return IList;
        }


        public static InstalmentEntity GetInstalmentByInsId(int Instalment_ID)
        {
            InstalmentEntity I = new InstalmentEntity();


            cmdText = "select * from Pay where Instalment_ID=@Instalm_ID";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Instalm_ID", Instalment_ID);
            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {

                    I=fillEntityFromReader(reader);
                }
                //      MessageBox.Show("Get"+IList.Count+ "Records");
                reader.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return I;
        }  
     

        public static List<InstalmentEntity> GetAllInstalment()
        {
            List<InstalmentEntity> IList = new List<InstalmentEntity>();

            cmdText = "select * from Pay ";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            try
            {
                con.Open();

                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                   IList.Add(fillEntityFromReader(reader));
                }
                reader.Close();
              
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }

            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
              
            }

            return IList;

        }

        public static void Add(InstalmentEntity I)
        {
            cmdText = "insert into Pay (Instalment_DueDate, Instalment_Amount  , Instalment_Penalty ,Instalment_Total ,Instalment_DateOfPay ,Instalment_Notes  ,Loan_ID ,Payed ,Payed_Amount ,Rest ,User_Id)values" +
                                      "(@Instalment_DueDate,@Instalment_Amount,@Instalment_Penalty ,@Instalment_Total ,@Instalment_DateOfPay,@Instalment_Notes,@Loan_ID,@Payed,@payed_Amount,@Rest,@UserId)";
              
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);
            cmd.Parameters.AddWithValue("@Instalment_DueDate", I.DueDate);
            cmd.Parameters.AddWithValue("@Instalment_Amount", I.Amount);
            cmd.Parameters.AddWithValue("@Instalment_Penalty", I.Penalty);
            cmd.Parameters.AddWithValue("@Instalment_Total", I.Total);
            //cmd.Parameters.AddWithValue("@Instalment_DateOfPay", I.DateOfPay);
            cmd.Parameters.AddWithValue("@Instalment_DateOfPay", "1-1-2001");
            cmd.Parameters.AddWithValue("@Instalment_Notes", I.Notes);
            cmd.Parameters.AddWithValue("@Loan_ID", I.Loan_ID);
            cmd.Parameters.AddWithValue("@Payed", I.payed);
            cmd.Parameters.AddWithValue("@payed_Amount", I.Payed_Amount);
            cmd.Parameters.AddWithValue("@Rest", I.Rest);
            cmd.Parameters.AddWithValue("@UserId", I.userId);
        
            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
              
               
            }
            catch(Exception ex){
                MessageBox.Show("Exception==>>"+ex.Message);
            }
            finally
            {
                if(con.State == ConnectionState.Open)
                    con.Close();
            }

            

        }

        public static bool Update(InstalmentEntity I)
        {
            Boolean flag = false;
            cmdText = "UPDATE Pay SET Instalment_DueDate=@Instalment_DueDate , Instalment_Amount=@Instalment_Amount , Instalment_Penalty=@Instalment_Penalty ,Instalment_Total=@Instalment_Total"+
                " ,Instalment_DateOfPay=@Instalment_DateOfPay ,Instalment_Notes=@Instalment_Notes , Loan_ID=@Loan_ID ,Payed=@Payed, Payed_Amount=@payed_Amount, Rest=@Rest, User_Id=@UserId  WHERE Instalment_ID=@Instalment_ID ";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            cmd.Parameters.AddWithValue("@Instalment_DueDate", I.DueDate);
            cmd.Parameters.AddWithValue("@Instalment_Amount", I.Amount);
            cmd.Parameters.AddWithValue("@Instalment_Penalty", I.Penalty);
            cmd.Parameters.AddWithValue("@Instalment_Total", I.Total);
            cmd.Parameters.AddWithValue("@Instalment_DateOfPay", I.DateOfPay);
            cmd.Parameters.AddWithValue("@Instalment_Notes", I.Notes);
            cmd.Parameters.AddWithValue("@Loan_ID", I.Loan_ID);
            cmd.Parameters.AddWithValue("@Payed", I.payed);
            cmd.Parameters.AddWithValue("@payed_Amount", I.Payed_Amount);
            cmd.Parameters.AddWithValue("@Rest", I.Rest);
            cmd.Parameters.AddWithValue("@UserId", I.userId);
            cmd.Parameters.AddWithValue("@Instalment_ID", I.Id);

        
            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();
                
                flag = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }

        public static bool UpdateInstNote(InstalmentEntity I)
        {
            Boolean flag = false;
            cmdText = "UPDATE Pay SET Instalment_Notes=@Instalment_Notes WHERE Instalment_ID=@Loan_ID ";
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cmd = new SqlCommand(cmdText, con);

            cmd.Parameters.AddWithValue("@Instalment_Notes", I.Notes);
            cmd.Parameters.AddWithValue("@Loan_ID", I.Id);


            try
            {
                con.Open();

                Int32 numOfRow = cmd.ExecuteNonQuery();

                flag = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception==>>" + ex.Message);
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

            return flag;
        }



             /*
                Delete using id or Serial 
             */
        public static void DeleteAll(Int32 loanID) {

            List<InstalmentEntity> list = Instalment.GetInstalment(loanID);
            foreach (InstalmentEntity i in list){
                Delete(i);
            }
        }
        public static bool Delete(InstalmentEntity I)
        {
            Boolean flag = false;
            cmdText = "Delete from Pay WHERE Instalment_ID=@Loan_ID";

           
                SqlConnection con = new SqlConnection(conString);
                SqlCommand cmd = new SqlCommand(cmdText, con);
                cmd.Parameters.AddWithValue("@Loan_ID", I.Id);
                try
                {
                    con.Open();

                    Int32 numOfRow = cmd.ExecuteNonQuery();
                    //MessageBox.Show(numOfRow + "Record Deleted");
                    flag = true;


                }
                catch (Exception ex)
                {
                    MessageBox.Show("Exception==>>" + ex.Message);
                }
                finally
                {
                    if (con.State == ConnectionState.Open)
                        con.Close();
                }
             
            return flag;
        }





   




        private static InstalmentEntity fillEntityFromReader(SqlDataReader reader)
        {
         

            InstalmentEntity I = new InstalmentEntity();

            I.Id = Int32.Parse(reader["Instalment_ID"].ToString());

            if (reader["Loan_ID"] != null)
                I.Loan_ID = Int32.Parse(reader["Loan_ID"].ToString());
            if (reader["Instalment_Amount"] != null)
                I.Amount = Int32.Parse(reader["Instalment_Amount"].ToString());
            if (reader["Instalment_Penalty"] != null)
                I.Penalty = Int32.Parse(reader["Instalment_Penalty"].ToString());
            if (reader["Instalment_DueDate"] != null)
                I.DueDate = (DateTime)reader["Instalment_DueDate"];
            if (reader["Instalment_DateOfPay"] != null)
                I.DateOfPay = (DateTime)reader["Instalment_DateOfPay"];
            if (reader["Instalment_Total"] != null)
                I.Total = Int32.Parse(reader["Instalment_Total"].ToString());
            if (reader["Instalment_Notes"] != null)
                I.Notes = reader["Instalment_Notes"].ToString();
            I.payed = bool.Parse(reader["Payed"].ToString());
            I.Payed_Amount = Int32.Parse(reader["Payed_Amount"].ToString());
            if (reader["User_Id"] != null && reader["User_Id"].ToString() !="") 
                I.userId = Int32.Parse(reader["User_Id"].ToString());
            I.Rest = Int32.Parse(reader["Rest"].ToString());
            return I;
        }

         
    }
}
